Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# FLASHBACK DATABASE

Purpose

Use the `FLASHBACK` `DATABASE` statement to return the database to a past time or system change number (SCN). This statement provides a fast alternative to performing incomplete database recovery.

Following a `FLASHBACK` `DATABASE` operation, in order to have write access to the flashed back database, you must reopen it with an `ALTER` `DATABASE` `OPEN` `RESETLOGS` statement.

See Also: Oracle Database Backup and Recovery Basics for more information on `FLASHBACK` `DATABASE`

Prerequisites

You must have the `SYSDBA` system privilege. A flash recovery area must have been prepared for the database. The database must have been put in `FLASHBACK` mode with an `ALTER` `DATABASE` `FLASHBACK` `ON` statement unless you are flashing the database back to a guaranteed restore point. The database must be mounted but not open.

See Also: Oracle Database Backup and Recovery Advanced User's Guide and the `ALTER` `DATABASE` ... flashback_mode_clause for information on putting the database in `FLASHBACK` mode CREATE RESTORE POINT for information on restore points and guaranteed restore points

Syntax

flashback_database::=

Description of the illustration flashback_database.gif

Semantics

When you issue a `FLASHBACK` `DATABASE` statement, Oracle Database first verifies that all required archived and online redo logs are available. If they are available, then it reverts all currently online datafiles in the database to the SCN or time specified in this statement.

- The amount of Flashback data retained in the database is controlled by the `DB_FLASHBACK_RETENTION_TARGET` initialization parameter and the size of the flash recovery area. You can determine how far back you can Flashback the database by querying the `V$FLASHBACK_DATABASE_LOG` view.

- If insufficient data remains in the database to perform the Flashback, then you can use standard recovery procedures to recover the database to a past point in time.

- If insufficient data remains for a set of datafiles, then the database returns an error. In this case, you can take those datafiles offline and reissue the statement to revert the remainder of the database. You can then attempt to recover the offline datafiles using standard recovery procedures.

See Also: Oracle Database Backup and Recovery Advanced User's Guide for more information on recovering datafiles

STANDBY

Specify `STANDBY` to revert the standby database to an earlier SCN or time. If the database is not a standby database, then the database returns an error. If you omit this clause, then `database` can be either a primary or a standby database.

See Also: Oracle Data Guard Concepts and Administration for information on how you can use `FLASHBACK` `DATABASE` on a standby database to achieve different delays

TO SCN Clause

Specify a system change number (SCN):

- `TO SCN` reverts the database back to its state at the specified SCN.

- `TO BEFORE SCN` reverts the database back to its state at the system change number just preceding the specified SCN.

You can determine the current SCN by querying the `CURRENT_SCN` column of the `V$DATABASE` view. This in turn lets you save the SCN to a spool file, for example, before running a high-risk batch job.

TO TIMESTAMP Clause

Specify a valid datetime expression.

- `TO TIMESTAMP` reverts the database back to its state at the specified timestamp.

- `TO BEFORE TIMESTAMP` reverts the database back to its state one second before the specified timestamp.

You can represent the timestamp as an offset from a determinate value, such as `SYSDATE`, or as an absolute system timestamp.

TO RESTORE POINT Clause

Specify this clause to flash back the database to the specified restore point. If you have not enabled flashback database, this is the only clause you can specify in this `FLASHBACK` `DATABASE` statement. If the database is not in `FLASHBACK` mode, as described in the "Prerequisites" section above, this is the only clause you can specify for this statement.

RESETLOGS

Specify `TO` `BEFORE` `RESETLOGS` to flash the database back to just before the last resetlogs operation (`ALTER` `DATABASE` `OPEN` `RESETLOGS`).

See Also: Oracle Database Backup and Recovery Basics for more information about this clause

Examples

Assuming that you have prepared a flash recovery area for the database and enabled media recovery, enable database `FLASHBACK` mode and open the database with the following statements:

    STARTUP MOUNT
    ALTER DATABASE FLASHBACK ON;
    ALTER DATABASE OPEN;

With your database open for at least a day, you can flash back the database one day with the following statements:

    SHUTDOWN DATABASE
    STARTUP MOUNT
    FLASHBACK DATABASE TO TIMESTAMP SYSDATE-1;